package com.sl.au.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.sl.au.entity.AfterSaleOutHouse;
import com.sl.au.entity.AfterSaleTicketAdvice;
import com.sl.au.entity.AfterSaleTicketBack;
import com.sl.au.entity.AfterSaleTicketTrance;

public interface AfterSaleOutHouseRepository  extends JpaRepository<AfterSaleOutHouse,String>{
	/**
	 * 售后外勤主界面数据
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select a from AfterSaleOutHouse a where a.isReceived =0 and a.user.id =?1 "
			+ "and (a.afterSaleTicket.unitName like '%'+?2+'%' "
			+ "or a.afterSaleTicket.afterSaleType like '%'+?2+'%' "
			+ "or a.afterSaleTicket.proposer like '%'+?2+'%' "
			+ "or a.afterSaleTicket.linkMan like '%'+?2+'%') ")
	Page<AfterSaleOutHouse> findOutHouseTicket(String userId,String key, Pageable pageable);

	/**
	 *  售后外勤主界面数据数量
	 * @param key
	 * @return
	 */
	@Query(" select count(*) from AfterSaleOutHouse a where a.isReceived =0 and a.user.id =?1 "
			+ "and (a.afterSaleTicket.unitName like '%'+?2+'%' "
			+ "or a.afterSaleTicket.afterSaleType like '%'+?2+'%' "
			+ "or a.afterSaleTicket.proposer like '%'+?2+'%' "
			+ "or a.afterSaleTicket.linkMan like '%'+?2+'%') ")
	long getCountBykey(String userId,String key);
	/**
	 * 当前登录人售后外勤主界面数据
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select a from AfterSaleOutHouse a where a.isReceived =1 and a.user.id =?1 "
			+ "and a.outState like '%'+?3+'%' "
			+ "and (a.afterSaleTicket.unitName like '%'+?2+'%' "
			+ "or a.afterSaleTicket.afterSaleType like '%'+?2+'%' "
			+ "or a.afterSaleTicket.manageCode like '%'+?2+'%' "
			+ "or a.afterSaleTicket.proposer like '%'+?2+'%' "
			+ "or a.afterSaleTicket.linkMan like '%'+?2+'%') ")
	Page<AfterSaleOutHouse> findMyOutHouseTicket(String userId,String key,String outState, Pageable pageable);

	/**
	 *  当前登录人售后外勤主界面数据数量
	 * @param key
	 * @return
	 */
	@Query(" select count(*) from AfterSaleOutHouse a where a.isReceived =1 and a.user.id =?1 "
			+ "and a.outState like '%'+?3+'%' "
			+ "and (a.afterSaleTicket.unitName like '%'+?2+'%' "
			+ "or a.afterSaleTicket.afterSaleType like '%'+?2+'%' "
			+ "or a.afterSaleTicket.manageCode like '%'+?2+'%' "
			+ "or a.afterSaleTicket.proposer like '%'+?2+'%' "
			+ "or a.afterSaleTicket.linkMan like '%'+?2+'%') ")
	long getMyCountBykey(String userId,String key,String outState);
	
	/**
	 *  获取跟踪服务单Tab2页数据
	 * @param ticketId
	 * @return
	 */
	@Query(" select a from AfterSaleTicketBack a where a.afterSaleTicket.id =?1  ")
	List<AfterSaleTicketBack> getTicketTab2Data(String ticketId);
	
	/**
	 *  获取跟踪服务单Tab3页数据
	 * @param ticketId
	 * @return
	 */
	@Query(" select a from AfterSaleTicketAdvice a where a.afterSaleTicket.id =?1  ")
	List<AfterSaleTicketAdvice> getTicketTab3Data(String ticketId);
	
	
	
   /**
    * 根据key获取售服务单回访与评价页面数据
    * @param key 查询条件
    * @return
    */
	@Query(" select a from AfterSaleOutHouse a where a.isFinish = 1"
			+ "and (a.afterSaleTicket.unitName like '%'+?1+'%' "
			+ "or a.afterSaleTicket.afterSaleType like '%'+?1+'%' "
			+ "or a.afterSaleTicket.proposer like '%'+?1+'%' "
			+ "or a.assess like '%'+?1+'%') ")
	Page<AfterSaleOutHouse> getTicketAssessByKey(String key, Pageable pageable);

	/**
	 * 根据key得到售后服务单回访与评价数据条数
	 * @param key 查询条件
	 * @return
	 */
	@Query(" select count(*) from AfterSaleOutHouse a where a.isFinish = 1"
			+ "and (a.afterSaleTicket.unitName like '%'+?1+'%' "
			+ "or a.afterSaleTicket.afterSaleType like '%'+?1+'%' "
			+ "or a.afterSaleTicket.proposer like '%'+?1+'%' "
			+ "or a.assess like '%'+?1+'%') ")
   long getAssessCountBykey(String key);
	/**
	 * 当前服务单跟踪情况数据
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select a from AfterSaleTicketTrance a where a.afterSaleTicket.id =?2 and a.user.id =?1  ")
	Page<AfterSaleTicketTrance> findTicketTrance(String userId,String ticketId, Pageable pageable);
	
	@Query(" select count(*)  from AfterSaleTicketTrance a where a.afterSaleTicket.id =?2 and a.user.id =?1  ")
    long getTickettranceCount(String userId,String ticketId);
	/**
	 * 当前服务单跟踪人员数据
	 * @param key
	 * @param pageable
	 * @return
	 */
	@Query(" select a from AfterSaleOutHouse a where a.afterSaleTicket.id =?1")
	Page<AfterSaleOutHouse> findTicketTrancePerson(String ticketId, Pageable pageable);
	/**
	 * 获取一个服务单所有跟踪人员数据
	 * @param ticketId
	 * @return
	 */
	@Query(" select count(*)  from AfterSaleOutHouse a where a.afterSaleTicket.id =?1")
	long getTickettranceCount(String ticketId);
	
	/**
	 * 获得服务单的所有派遣单
	 */
	@Query(" select a from AfterSaleOutHouse a where a.afterSaleTicket.id =?1")
	List<AfterSaleOutHouse> findAllOuts(String ticketId);
	
	/*
	 * 获得一个派遣单的所有活动记录
	 */
	@Query(" select a from AfterSaleTicketTrance a where a.afterSaleOutHouse.id =?1")
	Page<AfterSaleTicketTrance> findAllTrance(String outId, Pageable pageable);
	
	@Query(" select count(*) from AfterSaleTicketTrance a where a.afterSaleOutHouse.id =?1")
	long getTranceCount(String outId);
	
	/**
	 * 获得服务单的所有未接受的派遣单
	 */
	@Query(" select a from AfterSaleOutHouse a where a.afterSaleTicket.id =?1 and a.isReceived=0")
	Page<AfterSaleOutHouse> findAllNoReceivedOuts(String ticketId,Pageable pageable);
	
	@Query(" select count(*) from AfterSaleOutHouse a where a.afterSaleTicket.id =?1 and a.isReceived=0")
	long allNoReceivedCount(String ticketId);

	@Query(" select a from AfterSaleOutHouse a where (a.afterSaleTicket.unitName like '%'+?1+'%' "
			+ "or a.afterSaleTicket.afterSaleType like '%'+?1+'%' "
			+ "or a.afterSaleTicket.manageCode like '%'+?1+'%' "
			+ "or a.afterSaleTicket.proposer like '%'+?1+'%' "
			+ "or a.afterSaleTicket.linkMan like '%'+?1+'%') ")
	Page<AfterSaleOutHouse> getAllOutHouseTicket(String string, Pageable pageable);

	@Query(" select count(*) from AfterSaleOutHouse a where (a.afterSaleTicket.unitName like '%'+?1+'%' "
			+ "or a.afterSaleTicket.afterSaleType like '%'+?1+'%' "
			+ "or a.afterSaleTicket.manageCode like '%'+?1+'%' "
			+ "or a.afterSaleTicket.proposer like '%'+?1+'%' "
			+ "or a.afterSaleTicket.linkMan like '%'+?1+'%') ")
	long getAllCountBykey(String string);

	@Query(" select count(*) from AfterSaleTicketTrance a where a.afterSaleOutHouse.id =?1 " 
			+ "and (a.activeType like '%'+?2+'%' " 
			+ "or a.activeType like '%'+?2+'%' or a.user.name like '%'+?2+'%')")
	long getTranceCountBykey(String outId, String key);

	@Query(" select a from AfterSaleTicketTrance a where a.afterSaleOutHouse.id =?1 " 
			+ "and (a.activeType like '%'+?2+'%' " 
			+ "or a.activeType like '%'+?2+'%' or a.user.name like '%'+?2+'%')")
	Page<AfterSaleTicketTrance> findAllTranceBykey(String outId, String key, Pageable pageable);
}
